package org.datascooter.impl;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.logging.Logger;

import org.datascooter.cache.ConnectionBundle;
import org.datascooter.cache.ConnectionManager;
import org.datascooter.exception.SnipManagerException;
import org.datascooter.utils.DSSettings;
import org.datascooter.utils.policy.SnipType;

public abstract class SimpleExecutor {

	private static final String JDBC_MESSAGE = "\nJDBC message: ";
	private static final String STRING = "  -  ";
	private static final String ATTRIBUTE_INDEX = "\nattribute index : ";
	private static final String NN = "\n";
	private static final String ERROR_BY_INSERT_PARAMS = "***Error by insert params : ";
	public ConnectionBundle bundle;
	protected boolean supportPagination;
	protected boolean callParameterMetadata;

	public SimpleExecutor(boolean supportPagination, boolean callParameterMetadata) {
		this.supportPagination = supportPagination;
		this.callParameterMetadata = callParameterMetadata;
	}

	public abstract void execute(Snip snip) throws SQLException;

	public PreparedStatement getStatement(Snip snip) throws SQLException {
		return bundle.getStatement(snip);
	}

	public void prepare(String url) throws SQLException {
		if (bundle == null || bundle.connection.isClosed()) {
			bundle = ConnectionManager.getConnectionBundle(url);
		}
	}

	public void commit() throws SQLException {
		if (bundle != null) {
			bundle.connection.commit();
			ConnectionManager.releaseBundle(bundle);
			bundle = null;
		}
	}

	public void rollback() throws SQLException {
		if (bundle != null && !bundle.connection.isClosed()) {
			try {
				bundle.connection.rollback();
			} catch (Throwable e) {
				Logger.getLogger(getClass().getName()).severe("**SimpleExecutor error by rollback - " + e.getMessage());
			}
			try {
				ConnectionManager.releaseBundle(bundle);
			} catch (Throwable e) {
				Logger.getLogger(getClass().getName()).severe("**SimpleExecutor error by release bundle - " + e.getMessage());
			}
			bundle = null;
		}
	}

	protected void executeSimple(Snip parent, Snip snip) throws SQLException {
		PreparedStatement prepare = getStatement(snip);
		if (snip.type.equals(SnipType.CREATE)) {
			prepare.executeUpdate();
		} else {
			if (snip.getData() == null || snip.getData().length < 2 || !bundle.connection.getMetaData().supportsBatchUpdates()) {
				executeEach(snip.getData(), prepare);
			} else {
				executeBatch(snip.getData(), prepare, DSSettings.getBatchSize());
			}
		}
		prepare.clearParameters();
		if (snip.getFetch() != null) {
			for (Snip child : snip.getFetch()) {
				executeSimple(snip, child);
			}
		}
	}

	protected void executeCount(Snip snip) throws SQLException {
		PreparedStatement prepare = getStatement(snip);
		initParams(prepare, snip.getData(), 0, prepare.getParameterMetaData().getParameterCount());
		BigDecimal result = BigDecimal.ZERO;
		if (prepare.execute()) {
			ResultSet rs = prepare.getResultSet();
			rs.next();
			if (rs.getObject(1) != null) {
				result = result.add(rs.getBigDecimal(1));
			}
		} else {
			result = result.add(new BigDecimal(prepare.getUpdateCount()));
		}
		prepare.clearParameters();
		snip.setData(new Object[][] {
			new BigDecimal[] {
				result
			}
		});
	}

	private void executeBatch(Object[][] data, PreparedStatement prepare, int batchSize) throws SQLException {
		int startRow = 0;
		int stopRow = data.length >= batchSize ? batchSize : data.length;
		do {
			int count = prepare.getParameterMetaData().getParameterCount();
			for (int row = startRow; row < stopRow; row++) {
				initParams(prepare, data, row, count);
				prepare.addBatch();
			}
			prepare.executeBatch();
			prepare.clearBatch();
			startRow = stopRow;
			stopRow += batchSize;
			if (stopRow > data.length) {
				stopRow = data.length;
			}
		} while (startRow < data.length);
	}

	private void executeEach(Object[][] data, PreparedStatement prepare) throws SQLException {
		int row = 0;
		do {
			// if ((data == null || data[row] == null || data[row].length == 0) || data[row][0] != null) {
			initParams(prepare, data, row, prepare.getParameterMetaData().getParameterCount());
			prepare.executeUpdate();
			// }
			row++;
		} while (data != null && row < data.length);
	}

	private void initParams(PreparedStatement prepare, Object[][] data, int row, int count) throws SQLException {
		try {
			if (data != null && data.length > 0) {
				Object[] paramArray = data[row];
				if (paramArray != null) {
					int length = paramArray.length;
					for (int index = 0; index < count; index++) {
						try {
							if (index >= length) {
								prepare.setNull(index + 1, 0);
								continue;
							}
							if (paramArray[index] == null) {
								prepare.setNull(index + 1, 0);
							} else {
								if (callParameterMetadata) {
									prepare.getParameterMetaData();
								}
								prepare.setObject(index + 1, paramArray[index]);
							}
						} catch (Exception e) {
							e.printStackTrace();
							throw new SnipManagerException(ERROR_BY_INSERT_PARAMS + NN + Arrays.toString(paramArray) + ATTRIBUTE_INDEX + paramArray[index]
								+ STRING + index + JDBC_MESSAGE + e.getMessage() + NN, e);
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected void retrieve(Snip parent, Snip snip) throws SQLException {
		if (snip.query != null) {
			Collection<Object[]> collection = new HashSet<Object[]>();
			PreparedStatement prepare = getStatement(snip);
			if ((snip.parentIndex >= 0) && (parent != null) && (parent.getData() != null)) {
				for (Object[] array : parent.getData()) {
					retrieveInternal(snip, new Object[][] {
						new Object[] {
							array[snip.parentIndex]
						}
					}, collection, prepare);
				}
			} else {
				retrieveInternal(snip, snip.getData(), collection, prepare);
			}
			prepare.clearParameters();
			snip.setData(collection.toArray(new Object[collection.size()][0]));
		}
		if (snip.getFetch() != null) {
			for (Snip child : snip.getFetch()) {
				retrieve(snip, child);
			}
		}
	}

	private void retrieveInternal(Snip snip, Object[][] data, Collection<Object[]> collection, PreparedStatement prepare) throws SQLException {
		if (snip.query != null) {
			ResultSet rs = null;
			try {
				initParams(prepare, data, 0, prepare.getParameterMetaData().getParameterCount());
				prepare.setFetchSize(snip.fetchSize);
				if (prepare.execute()) {
					rs = prepare.getResultSet();
					if (supportPagination || (snip.fetchSize == 0)) {
						fullFetch(snip, rs, collection);
					} else {
						customPartFetch(snip, rs, collection);
					}
				}
			} finally {
				if (rs != null) {
					rs.close();
				}
			}
		}
	}

	private void fullFetch(Snip snip, ResultSet rs, Collection<Object[]> collection) throws SQLException {
		int columnCount = Math.min(rs.getMetaData().getColumnCount(), snip.fields.length);
		while (rs.next()) {
			Object[] array = new Object[columnCount];
			for (int column = 0; column < columnCount; column++) {
				try {
					array[column] = rs.getString(snip.fields[column]);
				} catch (Exception e) {
					// e.printStackTrace();
				}
			}
			collection.add(array);
		}
	}

	private void customPartFetch(Snip snip, ResultSet rs, Collection<Object[]> collection) throws SQLException {
		int count = 0;
		int columnCount = Math.min(rs.getMetaData().getColumnCount(), snip.fields.length);
		rs.setFetchSize(snip.fetchSize);
		while (rs.next()) {
			if (rs.getRow() <= snip.fromRow) {
				continue;
			}
			Object[] array = new Object[columnCount];
			for (int column = 0; column < columnCount; column++) {
				try {
					array[column] = rs.getString(snip.fields[column]);
				} catch (Exception e) {
					// e.printStackTrace();
				}
			}
			collection.add(array);
			count++;
			if (count >= snip.fetchSize) {
				break;
			}
		}
	}
}
